package br.com.ffm.praticas.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.LinkedList;
import java.util.List;

import br.com.ffm.praticas.bean.Category;
import br.com.ffm.praticas.connection.ConnectionDB;

public class CategoryDAO {
	
	/**
	 * Method that returns all Categories
	 * @return List<Category>
	 * @throws SQLException
	 */
	public List<Category> getCategories() throws SQLException {
		String query = "SELECT * FROM category ORDER BY description";
		List<Category> list = new LinkedList<Category>();
		ResultSet result = ConnectionDB.getInstance().executeReader(query);
		while (result.next()) {
			Category category = new Category();
			category.setId(result.getInt("id"));
			if (result.getString("description") != null)
				category.setDescription(result.getString("description"));
			list.add(category);
		}
		ConnectionDB.getInstance().close();
		return list;
	}
	
	/**
	 * Method that returns a specific Category for your id
	 * @param id Category Identifier
	 * @return Category
	 * @throws SQLException
	 */
	public Category getCategory(int id) throws SQLException {
		String query = "SELECT * FROM category WHERE (id = " + id + ")";
		Category category;
		ResultSet result = ConnectionDB.getInstance().executeReader(query);
		if (result.next()) {
			category = new Category();
			category.setId(result.getInt("id"));
			if (result.getString("description") != null)
				category.setDescription(result.getString("description"));
			ConnectionDB.getInstance().close();
			return category;
		}
		ConnectionDB.getInstance().close();
		return null;	
	}
	
	/**
	 * Method that returns a specific Category for your description
	 * @param description Category Description
	 * @return Category
	 * @throws SQLException
	 */
	public Category getCategory(String description) throws SQLException {
		String query = "SELECT * FROM category WHERE (description = '" + description + "')";
		Category category;
		ResultSet result = ConnectionDB.getInstance().executeReader(query);
		if (result.next()) {
			category = new Category();
			category.setId(result.getInt("id"));
			if (result.getString("description") != null)
				category.setDescription(result.getString("description"));
			ConnectionDB.getInstance().close();
			return category;
		}
		ConnectionDB.getInstance().close();
		return null;	
	}

}
